package com.kmdin.Tools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.kmdin.DB.SqlDB;



public class EmployeeDaoImpI implements IEmployeeDao {
	
	public static Connection conn=SqlDB.getConn();
	private static PreparedStatement pstmt;
	private ResultSet rs;
	
	
	 /**
     * 创建新员工
     */
	@Override
	public void create(EmployeeBean employeebean) throws Exception {
		// TODO Auto-generated method stub
		
		String sqls = "insert into Employee(Name,Dept,Age,Salary) values(?,?,?,?)";
		try {  
				pstmt=(PreparedStatement)conn.prepareStatement(sqls);
				//pstmt.setInt(1, employeebean.getEmployeeID());
				pstmt.setString(1, employeebean.getName());
				pstmt.setString(2, employeebean.getDept());
				pstmt.setInt(3, employeebean.getAge());
				pstmt.setDouble(4, employeebean.getSalary());
				pstmt.executeUpdate();
				pstmt.close();
		} catch (Exception e) {   
           e.printStackTrace();
        } 	
		
	}

	
	/**
	 * 修改员工
	 * @param employeebean
	 */
	@Override
	public void update(EmployeeBean employeebean) throws Exception {
		// TODO Auto-generated method stub
		String sql = "update Employee set Name=?,Dept=?,Age=?,Salary=? where EmployeeID=?";   
        try {   
        	pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, employeebean.getEmployeeID());
			pstmt.setString(2, employeebean.getName());
			pstmt.setString(3, employeebean.getDept());
			pstmt.setInt(4, employeebean.getAge());
			pstmt.setDouble(5, employeebean.getSalary());
			pstmt.executeUpdate();
			pstmt.close();
        } catch (Exception e) {   
        	e.printStackTrace();   
        } 
    }   
	
	/***
	 * 删除员工
	 * @param employeebean
	 */
	@Override
	public void delete(int emloyeeid) throws Exception {
		// TODO Auto-generated method stub
		String sql = "delete from Employee where EmployeeID =?" ;   
        try {   
        	pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, emloyeeid);
			pstmt.executeUpdate();
			pstmt.close();
        } catch (Exception e) {   
        	e.printStackTrace();
        	} 
	}

	
	/***
	 * 取得指定的员工信息
	 * @param employeebean
	 * @return
	 */
	@Override
	public EmployeeBean getEmployee(String Name) throws Exception {
		String sql = "select * from Employee where Name=?" ;  
		EmployeeBean em=null;
        try {   
        	pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, Name);
			rs=pstmt.executeQuery();
			if(rs.next()){
				em=new EmployeeBean();
				//System.out.print(rs.getInt("EmployeeID"));
				System.out.print("员工姓名:"+rs.getString("Name"));
				System.out.print("员工所在部门:"+rs.getString("Dept"));
				System.out.print("员工年龄:"+rs.getInt("Age"));
				System.out.print("员工工资:"+rs.getDouble("Salary"));
			}
			rs.close();
			pstmt.close();
        } catch (Exception e) {   
        	e.printStackTrace();     
    	} 
		return em;
		// TODO Auto-generated method stub
		
	}

	/***
	 * 取得所有员工列表
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List getAll() throws Exception {
		// TODO Auto-generated method stub
		List all=new ArrayList<>();
		String sql = "select EmployeeID, Name ,Dept,Age,Salary from Employee " ;  
		 try {   
	        	pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
				while(rs.next()){
				EmployeeBean em=new EmployeeBean();
				em=new EmployeeBean();
				em.setEmployeeID(rs.getInt("EmployeeID"));
				em.setName(rs.getString("Name"));
				em.setDept(rs.getString("Dept"));
				em.setAge(rs.getInt("Age"));
				em.setSalary(rs.getDouble("Salary"));
				all.add(em);
				System.out.print("员工姓名:"+rs.getString("Name"));
				System.out.print("员工所在部门:"+rs.getString("Dept"));
				System.out.print("员工年龄:"+rs.getInt("Age"));
				System.out.print("员工工资:"+rs.getDouble("Salary"));
				}
				rs.close();
				pstmt.close();
	        } catch (Exception e) {   
	        	e.printStackTrace();
} 
		return all;
	}

}

